
package com.scalar.chiptrack.users.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.ClientInfo;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Region;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.proformainvoice.BankInfo;
import com.scalar.chiptrack.operations.reports.DBColumn;
import com.scalar.chiptrack.users.FunctionAccess;
import com.scalar.chiptrack.users.Functionality;
import com.scalar.chiptrack.users.Level;
import com.scalar.chiptrack.users.LevelAccess;
import com.scalar.chiptrack.users.SalesInfo;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.UserGroup;
import com.scalar.chiptrack.utils.*;
import com.scalar.chiptrack.sales.ContactPerson;


/* Saritha on 11/15/2004 added ccatsnumber and its related functions*/
public class UserDAO implements Constants {


    private UserDAO() {
    }


    public static String getEmailByUserId(String userId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        String email = null;
        ResultSet rset = null;

        if (userId == null)
        {
            return email;
        }

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getEmailByUserId"));
            pstmt.setString(1, userId);
            rset = pstmt.executeQuery();

            if (rset.next()) {
                email = rset.getString("Email");
                //System.out.println("Email is = " + email);
                if (email != null) {
                    email = email.trim();
                }
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get email from database for userId: " + userId, sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return email;
    }

    public static String loadEmailByInternalUserId( DBTransaction dbTransaction, String userId ) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        String email            = null;
        ResultSet rset          = null;

        if( userId == null )
        {
            return email;
        }

        try
        {

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT Email From User_Table WHERE User_ID = ? AND User_Type = 0" );
            pstmt.setString(1, userId);
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                email = rset.getString( "Email" );
                //System.out.println( "Email is = " + email );
                if( email != null )
                {
                    email = email.trim();
                }
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get email from database for userId: " + userId, sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return email;
    }
    public static ArrayList loadAllRegions() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList regionList = new ArrayList();
        ResultSet rset = null;
        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllRegions"));
            rset = pstmt.executeQuery();
            Region region = null;
            while (rset.next()) {
                region = new Region(rset.getInt("Region_ID"), rset.getString("Region_Name"));
                regionList.add(region);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Regions from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return regionList;
    }
    public static ArrayList loadUsersWithModifyFunctionAccess(String functionName) throws ScalarException
    {
        return loadUsersWithModifyFunctionAccess( null, functionName );
    }

    public static ArrayList loadUsersWithModifyFunctionAccess(DBTransaction dbTransaction, String functionName) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        if (functionName == null)
        {
            return userList;
        }

        try
        {

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "users.loadUsersWithModifyFunctionAccess" ) );
            pstmt.setString( 1, functionName );
            rset = pstmt.executeQuery();
            User user = null;

            while ( rset.next() )
            {
                user = new User(    rset.getString ( "User_ID" ),
                                    rset.getString ( "First_Name" ),
                                    rset.getString ( "Last_Name" ),
                                    rset.getString ( "Email" ),
                                    rset.getInt ( "Group_ID" ),
                                    rset.getInt ( "Level_ID" ) );

                user.setGroupName(rset.getString ("Group_Name"));
                user.setAddressId(rset.getInt ("Address_ID"));
                user.setRegionId(rset.getInt ("Region_ID"));
                user.setRegionName(rset.getString ("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setExpiresOn(rset.getString("Expires_On"));

                user.setReceiveNotifications( ( rset.getInt( "Send_Notifications" ) == 0 ) ? "on" : "off" );
                user.setDisabled( ( rset.getInt( "Disabled" ) == 0 ) ? true : false );
                user.setSalesAckMail( ( rset.getInt("Sales_Ack_Mail") == 0 ) ? true : false );
                user.setQuoteAckMail( ( rset.getInt("Quote_Ack_Mail") == 0 ) ? true : false );
                user.setRmaAckMail( ( rset.getInt("Rma_Ack_Mail") == 0 ) ? true : false );
                userList.add( user );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database based on Modify Functionality Access: \"" + functionName + "\"", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return userList;
    }


    public static ArrayList loadUsersByFunctionAccess(String functionName) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        if (functionName == null) {
            return userList;
        }

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.loadUsersByFunctionAccess"));
            pstmt.setString(1, functionName);

            rset = pstmt.executeQuery();
            User user = null;

            while (rset.next())
            {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString ("Group_Name"));
                user.setAddressId(rset.getInt ("Address_ID"));
                user.setRegionId(rset.getInt ("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");

                userList.add(user);
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database based on Functionality Access: \"" + functionName + "\"", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return userList;
    }


    public static ArrayList loadFunctionAccessListByUserId(String userId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList functionAccessList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectFunctionAccessListByUserId"));
            pstmt.setString(1, userId);

            rset = pstmt.executeQuery();
            FunctionAccess functionAccess = null;

            while (rset.next()) {
                functionAccess = new FunctionAccess(userId,
                rset.getInt("Group_ID"),
                rset.getInt("Function_ID"),
                ((rset.getInt("FAT_View") == 0) ? true : false),
                ((rset.getInt("FAT_Modify") == 0) ? true : false),
                ((rset.getInt("FAT_Delete") == 0) ? true : false));
                functionAccessList.add(functionAccess);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not FucntionAccess list from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return functionAccessList;
    }



    /**
     *  If levelId == -1
     *      -- insert a new row in Level Table
     *      -- retrieve the Level_ID from Level Table
     *      -- Inserts a row in Level_Access Table
     *
     *  else if levelID != -1
     *      -- delete all rows with Level_ID
     *      -- insert new rows
     *
     * @param levelId
     * @param levelName
     * @param levelAccessList
     *
     * @throws ScalarException if there is any problem with database interaction
     */
    public static void storeLevelAccessList(int levelId, String levelName, Iterator levelAccessList) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        ResultSet rset = null;

        if (levelAccessList == null) {
            return;
        }

        try {
            conn = DataSource.getConnection();

            if (levelId == -1) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertLevel"));
                pstmt.setString(1, levelName);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement("SELECT @@IDENTITY as level_id");
                rset = pstmt.executeQuery();
                if (rset.next() != false) {
                    levelId =  rset.getInt("level_id");
                }
                rset.close();
                pstmt.close();
                pstmt = null;

            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.deleteLevelAccessRowByLevelId"));
                pstmt.setInt(1, levelId);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertLevelAccessRow"));

            LevelAccess levelAccess = null;
            while (levelAccessList.hasNext()) {
                levelAccess = (LevelAccess) levelAccessList.next();

                pstmt.setInt(1, levelId);
                pstmt.setInt(2, levelAccess.getGroupId());
                pstmt.setInt(3, levelAccess.getFunctionId());
                pstmt.setInt(4, (levelAccess.getView() ? 0 : 1));
                pstmt.setInt(5, (levelAccess.getModify() ? 0 : 1));
                pstmt.setInt(6, (levelAccess.getDelete() ? 0 : 1));

                pstmt.addBatch();
            }

            pstmt.executeBatch();


            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert LevelAccess List into database", sqle);
        }

        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }


    public static void updateLevelAccessList(Iterator levelAccessList) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if (levelAccessList == null) {
            return;
        }

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateLevelAccessRow"));

            LevelAccess levelAccess = null;

            while (levelAccessList.hasNext()) {
                levelAccess = (LevelAccess) levelAccessList.next();

                pstmt.setInt(1, levelAccess.getGroupId());
                pstmt.setInt(2, levelAccess.getFunctionId());
                pstmt.setInt(3, (levelAccess.getView() ? 0 : 1));
                pstmt.setInt(4, (levelAccess.getModify() ? 0 : 1));
                pstmt.setInt(5, (levelAccess.getDelete() ? 0 : 1));
                pstmt.setInt(6, levelAccess.getLevelId());

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not update LevelAccess List into database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }



    public static ArrayList loadLevelAccessListByLevelId(int levelId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList levelAccessList = new ArrayList();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectLevelAccessListByLevelId"));
            pstmt.setInt(1, levelId);

            rset = pstmt.executeQuery();
            LevelAccess levelAccess = null;

            while (rset.next()) {
                levelAccess = new LevelAccess(levelId,
                rset.getInt("Group_ID"),
                rset.getInt("Function_ID"),
                ((rset.getInt("LA_View") == 0) ? true : false),
                ((rset.getInt("LA_Modify") == 0) ? true : false),
                ((rset.getInt("LA_Delete") == 0) ? true : false));
                levelAccessList.add(levelAccess);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not LevelAccess list from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return levelAccessList;
    }


    public static void updateAddress(DBTransaction dbTransaction, Address address) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        int addressId = 1;
        ResultSet rset = null;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateAddress"));

            pstmt.setString(1, address.getAddress1());
            pstmt.setString(2, address.getAddress2());
            pstmt.setString(3, address.getCity());
            pstmt.setString(4, address.getState());
            pstmt.setString(5, address.getZipCode());
            pstmt.setString(6, address.getCountry());
            pstmt.setInt(7, address.getAddressId());

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not update Address into database", sqle);
        }
        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static void deleteUser(String userName) throws ScalarException {

        if (userName == null) {
            return;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.deleteUser"));

            pstmt.setString(1, userName);
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not delete User information into database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static void activateUser(String userName) throws ScalarException {

        if (userName == null) {
            return;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.activateUser"));

            pstmt.setString(1, userName);
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not activate the User", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static void updateUserSettings(DBTransaction dbTransaction,
                                          String userId,
                                          String encryptedPassword,
                                          String firstName,
                                          String lastName,
                                          String phoneNumber,
                                          String faxNumber,
                                          int userGroupId,
                                          int levelId,
                                          int regionId,
                                          String email,
                                          String expiresOn,
                                          String receiveNotifications,
                                          boolean salesAckMail,
                                          boolean shipAlerts,
                                          boolean proformaInvoice,
                                          boolean packingSlip,
                                          boolean quoteAckMail,
                                          boolean rmaAckMail,
                                          int userType) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( encryptedPassword == null )
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateUserSettings"));

                pstmt.setInt    ( 1, userGroupId);
                pstmt.setString ( 2, firstName);
                pstmt.setString ( 3, lastName);
                pstmt.setString ( 4, phoneNumber);
                pstmt.setString ( 5, faxNumber);
                pstmt.setInt    ( 6, levelId);
                pstmt.setInt    ( 7, regionId);
                pstmt.setString ( 8, email);
                pstmt.setString ( 9, expiresOn);
                pstmt.setInt    ( 10, ( ( "on".equalsIgnoreCase( receiveNotifications ) ) ? 0 : 1 ) );
                pstmt.setInt    ( 11, ( salesAckMail    == false ) ? 1 : 0 );
                pstmt.setInt    ( 12, ( shipAlerts      == false ) ? 1 : 0 );
                pstmt.setInt    ( 13, ( proformaInvoice == false ) ? 1 : 0 );
                pstmt.setInt    ( 14, ( packingSlip     == false ) ? 1 : 0 );
                pstmt.setInt    ( 15, ( quoteAckMail    == false ) ? 1 : 0 );
                pstmt.setInt    ( 16, ( rmaAckMail      == false ) ? 1 : 0 );
                pstmt.setInt    ( 17, userType );
                pstmt.setString ( 18, userId);
            }
            else
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateUserSettingsAndPassword"));

                pstmt.setInt    ( 1, userGroupId);
                pstmt.setString ( 2, encryptedPassword);
                pstmt.setString ( 3, firstName);
                pstmt.setString ( 4, lastName);
                pstmt.setString ( 5, phoneNumber);
                pstmt.setString ( 6, faxNumber);
                pstmt.setInt    ( 7, levelId);
                pstmt.setInt    ( 8, regionId);
                pstmt.setString ( 9, email);
                pstmt.setString ( 10, expiresOn);
                pstmt.setInt    ( 11, ( ( "on".equalsIgnoreCase( receiveNotifications ) ) ? 0 : 1 ) );
                pstmt.setInt    ( 12, ( salesAckMail    == false) ? 1 : 0 );
                pstmt.setInt    ( 13, ( shipAlerts      == false) ? 1 : 0 );
                pstmt.setInt    ( 14, ( proformaInvoice == false) ? 1 : 0 );
                pstmt.setInt    ( 15, ( packingSlip     == false) ? 1 : 0 );
                pstmt.setInt    ( 16, ( quoteAckMail    == false) ? 1 : 0 );
                pstmt.setInt    ( 17, ( rmaAckMail    == false) ? 1 : 0 );
                pstmt.setInt    ( 18, userType );
                pstmt.setString ( 19, userId);

            }

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not Update User information into database", sqle);
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    // Password Change
    // Added on Mar 04, 2003 -- Balaram Putta
    public static void changePassword(DBTransaction dbTransaction, String userId, String oldPassword, String encryptedNewPassword) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        if ((oldPassword == null) || ((userId == null) || (userId.trim().length() == 0)) || (encryptedNewPassword == null)) {
            return;
        }

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getPasswordByUserId"));
            pstmt.setString(1, userId);
            rset = pstmt.executeQuery();

            String oldPasswordFromDB = null;
            if (rset.next()) {
                oldPasswordFromDB = rset.getString("Password");
            }

            rset.close();
            pstmt.close();
            pstmt = null;

            if ((oldPasswordFromDB == null) || (MessageDigestUtils.verify(oldPassword.trim(), StringUtils.toByteArray(oldPasswordFromDB.trim(), ",")) == true)) {

                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.changePassword"));
                pstmt.setString(1, encryptedNewPassword);
                pstmt.setString(2, userId);

                pstmt.executeUpdate();
            }
            else {
                throw new ScalarException("Password mismatch");
            }

            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not change the password for user: " + userId + ", " + sqle.getMessage(), sqle);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {

                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static void updateUserProfile(DBTransaction dbTransaction,
    String userId,
    String oldPassword,
    String newPassword,
    String firstName,
    String lastName,
    String phoneNumber,
    String faxNumber,
    String email,
    String receiveNotifications) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            if (newPassword != null) {
                // update password with new-password if old-password matches...

                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.changePassword"));
                pstmt.setString(1, newPassword);
                pstmt.setString(2, userId);
                pstmt.setString(3, oldPassword);

                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateUserProfile"));

            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, email);
            pstmt.setString(4, phoneNumber);
            pstmt.setString(5, faxNumber);
            pstmt.setInt(6, (("on".equalsIgnoreCase(receiveNotifications)) ? 0 : 1));
            System.out.println("receiveNotifications in userDAO - " + receiveNotifications);
            pstmt.setString(7, userId);

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not Update User information into database", sqle);
        }
        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static Address loadAddressById(int id) throws ScalarException {
        Address address = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;


        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAddressById"));
            pstmt.setInt(1, id);

            rset = pstmt.executeQuery();

            if (rset.next() == false) {
                // No address record found
                // log it
                System.out.println("No address record found");
            }
            else {
                address = new Address( rset.getInt( "Address_ID" ),
                rset.getString( "Address_1" ),
                rset.getString( "Address_2" ),
                rset.getString( "City" ),
                rset.getString( "State" ),
                rset.getString( "Zipcode" ),
                rset.getString( "Country" ),
                rset.getString( "Phone_Number" ),
                rset.getString( "Fax_Number" ),
                rset.getString( "Email" ) );
                address.setCellNumber(rset.getString( "Cell_Number" ));
                address.setAddress3(rset.getString( "Address_3" ));
                address.setAddress4(rset.getString( "Address_4" ));

            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Address from database (address id = " + id + ")", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return address;
    }


    public static int insertAddress(DBTransaction dbTransaction, String address1, String address2, String city, String state, String zipCode, String country) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        int addressId = 1;
        ResultSet rset = null;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertAddress"));

            pstmt.setString(1, address1);
            pstmt.setString(2, address2);
            pstmt.setString(3, city);
            pstmt.setString(4, state);
            pstmt.setString(5, zipCode);
            pstmt.setString(6, country);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("SELECT @@IDENTITY as level_id");
            rset = pstmt.executeQuery();
            while (rset.next()) {
                addressId = rset.getInt("level_id");
                //System.out.println("address id = " + addressId);
            }

            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert address into database", sqle);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return addressId;
    }


    public static User loadUser(String userId, String password) throws ScalarException {
        return loadUser(userId, password, false);
    }

    public static User loadUser(String userId, String password, boolean loadEvenIfItsExpiredOrDisabled) throws ScalarException {

        if ((userId == null) || (userId.trim().length() == 0)) {
            return null;
        }

        User user               = null;
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String functionName     = null;
        String samplePackingSlipFuntionality = Functionality.SAMPLE_PACKING_SLIP;

        try {
            //System.out.println("User id passed "+userId);
            conn = DataSource.getConnection();

            if (loadEvenIfItsExpiredOrDisabled == true) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUserByUserIdEvenIfExpiredOrDisabled"));
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUserByUserId"));
            }

            pstmt.setString(1, userId);

            rset = pstmt.executeQuery();

            if (rset.next() == false) {
                // Invalid User ID
                // log it
                throw new ScalarException("Invalid user id");
            }
            //else if ((password == null) || (password.trim().equals(rset.getString ("Password").trim()) == true)) {
            else if ( ( password == null) || (MessageDigestUtils.verify( password.trim(), StringUtils.toByteArray( rset.getString("Password").trim(), ",") ) == true ) )
            {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setPassword(rset.getString("Password"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setLevelId(rset.getInt("Level_ID")); //added by chandrasekhar 0n 8th nov 2004
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");
                user.setDisabled((rset.getInt("Disabled") == 0) ? true : false);
                user.setSalesAckMail( rset.getString("Sales_Ack_Mail") != null && rset.getString("Sales_Ack_Mail").equals("0") ? true : false );
                user.setShipAlerts ( rset.getString("Ship_Alerts") != null &&  rset.getString("Ship_Alerts").equals("0") ? true : false );
                user.setProformaInvoice ( rset.getString("Proforma_Invoice_Mail") != null &&  rset.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                user.setPackingSlip ( rset.getString("Packing_Slip_Mail") != null &&  rset.getString("Packing_Slip_Mail").equals("0") ? true : false );
                user.setQuoteAckMail( rset.getString("Quote_Ack_Mail") != null && rset.getString("Quote_Ack_Mail").equals("0") ? true : false );
                user.setRmaAckMail( rset.getString("Rma_Ack_Mail") != null && rset.getString("Rma_Ack_Mail").equals("0") ? true : false );
                user.setUserType( rset.getInt ( "USER_TYPE" ) );
                user.setCustomerId( rset.getInt ( "CUSTOMER_ID" ) );
                user.setVendorId( rset.getInt ( "VENDOR_ID" ) );
                user.setSalesPersonId( rset.getInt ( "SALESPERSON_ID" ) );


                rset.close();
                rset = null;

                pstmt.close();
                pstmt = null;

                // Load functionality Access Map
                HashMap functionAccessMap = new HashMap();
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectFunctionAccessListByUserId"));
                pstmt.setString(1, userId);

                rset = pstmt.executeQuery();
                FunctionAccess functionAccess = null;
                while (rset.next())
                {
                    functionAccess = new FunctionAccess(userId,
                    rset.getInt("Group_ID"),
                    rset.getInt("Function_ID"),
                    ((rset.getInt("FAT_View") == 0) ? true : false),
                    ((rset.getInt("FAT_Modify") == 0) ? true : false),
                    ((rset.getInt("FAT_Delete") == 0) ? true : false));

                    functionName = rset.getString("Function_Name").trim().toLowerCase();

                    if( "Marketing".equalsIgnoreCase( user.getGroupName() ) && samplePackingSlipFuntionality.equalsIgnoreCase( functionName ) && user.isBoardKitManagementAccess() == false )
                    {
                        user.setBoardKitManagementAccess( true );
                    }

                    functionAccessMap.put( functionName, functionAccess );
                }
                user.setFunctionAccessMap( functionAccessMap );

            }
            else
            {
                // Invalid Password
                // Log it
                System.out.println(rset.getString("Password") + " Invalid password");
                throw new ScalarException("Invalid password");
            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return user;
    }
    /**
     * This method is used to create the User by using only user name
     *
     * @param userId
     * @return User
     * @throws ScalarException
     */
    public static User loadUser( String userId ) throws ScalarException {

        if ((userId == null) || (userId.trim().length() == 0)) {
            return null;
        }

        User user = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUserByUserId"));
            pstmt.setString( 1, userId );
            rset = pstmt.executeQuery();

            if (rset.next() == false) {
                // Invalid User ID
                // log it
                throw new ScalarException("Invalid user id");
            }
            else {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));
                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setPassword(rset.getString("Password"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setLevelId(rset.getInt("Level_ID"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");
                user.setDisabled((rset.getInt("Disabled") == 0) ? true : false);
                user.setSalesAckMail( rset.getString("Sales_Ack_Mail") != null && rset.getString("Sales_Ack_Mail").equals("0") ? true : false );
                user.setShipAlerts ( rset.getString("Ship_Alerts") != null &&  rset.getString("Ship_Alerts").equals("0") ? true : false );
                user.setProformaInvoice ( rset.getString("Proforma_Invoice_Mail") != null &&  rset.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                user.setPackingSlip ( rset.getString("Packing_Slip_Mail") != null &&  rset.getString("Packing_Slip_Mail").equals("0") ? true : false );
                user.setQuoteAckMail( rset.getString("Quote_Ack_Mail") != null && rset.getString("Quote_Ack_Mail").equals("0") ? true : false );
                user.setRmaAckMail( rset.getString("Rma_Ack_Mail") != null && rset.getString("Rma_Ack_Mail").equals("0") ? true : false );
                user.setVendorId( rset.getInt ( "VENDOR_ID" ) );
                user.setUserType( rset.getInt ( "USER_TYPE" ) );

                rset.close();
                pstmt.close();
                pstmt = null;
                // Load functionality Access Map
                HashMap functionAccessMap = new HashMap();
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectFunctionAccessListByUserId"));
                pstmt.setString(1, userId);

                rset = pstmt.executeQuery();
                FunctionAccess functionAccess = null;
                while (rset.next()) {
                    functionAccess = new FunctionAccess(userId,
                    rset.getInt("Group_ID"),
                    rset.getInt("Function_ID"),
                    ((rset.getInt("FAT_View") == 0) ? true : false),
                    ((rset.getInt("FAT_Modify") == 0) ? true : false),
                    ((rset.getInt("FAT_Delete") == 0) ? true : false));
                    functionAccessMap.put(rset.getString("Function_Name").trim().toLowerCase(), functionAccess);
                }
                user.setFunctionAccessMap(functionAccessMap);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return user;
    }

    public static ArrayList loadUsersByFirstNameAndLastName(String firstName, String lastName, boolean loadExpiredAndDisabledUsers) throws ScalarException {

        if ((firstName == null) && (lastName == null)) {
            return null;
        }

        if (firstName == null) {
            firstName = "";
        }
        if (lastName == null) {
            lastName = "";
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if (loadExpiredAndDisabledUsers == true) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUsersByFirstNameAndLastNameIncludingExpiredAndDisabledUsers"));
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUsersByFirstNameAndLastName"));
            }

            pstmt.setString(1, firstName + "%");
            pstmt.setString(2, lastName + "%");

            rset = pstmt.executeQuery();
            User user = null;

            while (rset.next()) {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");
                user.setDisabled((rset.getInt("Disabled") == 0) ? true : false);

                userList.add(user);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return userList;
    }



    public static ArrayList loadUsersByGroupName(String groupName, boolean loadExpiredAndDisabledUsers) throws ScalarException {

        if (groupName == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if (loadExpiredAndDisabledUsers == true) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUsersByGroupNameIncludingExpiredAndDisabledUsers"));
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUsersByGroupName"));
            }

            pstmt.setString(1, groupName);

            rset = pstmt.executeQuery();
            User user = null;

            while (rset.next())
            {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");
                user.setDisabled((rset.getInt("Disabled") == 0) ? true : false);

                userList.add(user);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Users from database by group: " + groupName, sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return userList;
    }


    public static ArrayList loadUsersByRegionName(String regionName) throws ScalarException {

        if (regionName == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.loadUsersByRegionName"));
            pstmt.setString(1, regionName);

            rset = pstmt.executeQuery();
            User user = null;

            while (rset.next()) {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");

                userList.add(user);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database for Region: " + regionName, sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return userList;
    }


    public static ArrayList loadAllUserIDs(boolean loadExpiredAndDisabledUsers) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if (loadExpiredAndDisabledUsers == true) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllUserIDsIncludingExpiredAndDisabledUsers"));
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllUserIDs"));
            }

            rset = pstmt.executeQuery();
            String userId = null;

            while (rset.next()) {
                userId = rset.getString("User_ID");
                userList.add(new org.apache.struts.util.LabelValueBean(userId, userId));
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load User IDs from database", sqle);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return userList;
    }



    public static ArrayList loadAllUsers(boolean loadExpiredAndDisabledUsers, int userType) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if (loadExpiredAndDisabledUsers == true) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllUsersIncludingExpiredAndDisabledUsers"));
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllUsers"));
            }
            if ( Constants.USER_TYPE_ALL == userType )
            {
                pstmt.setString ( 1, "%" );
            }
            else
            {
                pstmt.setInt ( 1, userType );
            }
            rset = pstmt.executeQuery();
            User user = null;

            while (rset.next()) {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");
                user.setDisabled((rset.getInt("Disabled") == 0) ? true : false);
                user.setUserType( rset.getInt ( "USER_TYPE" ) );

                userList.add(user);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return userList;
    }
    public static int insertNewRegion( DBTransaction dbTransaction, String newRegionName) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
        int regionId            = 0;
        try
        {

            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("insert INTO Regions_Table (region_name, Deactivated) values (?, 0)");
            pstmt.setString ( 1, newRegionName);

            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            
            pstmt = conn.prepareStatement("SELECT @@IDENTITY as region_id");
            rset = pstmt.executeQuery();
            while (rset.next()) {
                regionId = rset.getInt("region_id");

            }
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not store new Region into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
       return regionId;
    }
    public static void insertUser(  DBTransaction dbTransaction,
                                     String userId,
                                     String password,
                                     String firstName,
                                     String lastName,
                                     int groupId,
                                     int levelId,
                                     int addressId,
                                     int regionId,
                                     String email,
                                     String phoneNumber,
                                     String faxNumber,
                                     String expiresOn,
                                     String receiveNotifications,
                                     /*String newRegion,*/
                                     boolean salesAckMail,
                                     boolean shipAlerts,
                                     boolean proformaInvoice,
                                     boolean packingSlip, boolean quoteAckMail,  boolean rmaAckMail, int userType ) throws ScalarException 
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {

            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertUser"));
            pstmt.setString ( 1, userId);
            pstmt.setString ( 2, password);
            pstmt.setString ( 3, firstName);
            pstmt.setString ( 4, lastName);
            pstmt.setInt    ( 5, groupId);
            pstmt.setInt    ( 6, levelId);
            pstmt.setInt    ( 7, addressId);
            pstmt.setInt    ( 8, regionId);
            pstmt.setString ( 9, email);
            pstmt.setString ( 10, phoneNumber);
            pstmt.setString ( 11, faxNumber);
            pstmt.setString ( 12, expiresOn);
            /*pstmt.setString ( 13, newRegion);*/
            pstmt.setInt    ( 13, ( ( "on".equalsIgnoreCase( receiveNotifications ) ) ? 0 : 1) );
            pstmt.setInt    ( 14, ( salesAckMail    == false ) ? 1 : 0 );
            pstmt.setInt    ( 15, ( shipAlerts      == false ) ? 1 : 0 );
            pstmt.setInt    ( 16, ( proformaInvoice == false ) ? 1 : 0 );
            pstmt.setInt    ( 17, ( packingSlip     == false ) ? 1 : 0 );
            pstmt.setInt    ( 18, ( quoteAckMail    == false ) ? 1 : 0 );
            pstmt.setInt    ( 19, ( rmaAckMail    == false ) ? 1 : 0 );
            pstmt.setInt    ( 20, userType );

            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not store User into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static void insertFunctionAccessList(DBTransaction dbTransaction, boolean isUpdate, String userId, Iterator functionAccessList) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if (functionAccessList == null) {
            return;
        }
        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            if (isUpdate == true) {

                // delete existing rows with given userId

                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.deleteFunctionAccessByUserId"));
                pstmt.setString(1, userId);

                pstmt.executeUpdate();

                pstmt.close();
                pstmt = null;
            }


            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertFunctionAccessRow"));

            FunctionAccess functionAccess = null;

            while (functionAccessList.hasNext()) {
                functionAccess = (FunctionAccess) functionAccessList.next();
                pstmt.setString(1, functionAccess.getUserId());
                pstmt.setInt(2, functionAccess.getGroupId());
                pstmt.setInt(3, functionAccess.getFunctionId());
                pstmt.setInt(4, (functionAccess.getView() ? 0 : 1));
                pstmt.setInt(5, (functionAccess.getModify() ? 0 : 1));
                pstmt.setInt(6, (functionAccess.getDelete() ? 0 : 1));

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert FunctionAccess List into database", sqle);
        }
        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static ArrayList loadAllLevels() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList levelList = new ArrayList();
        ResultSet rset = null;
        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllLevels"));

            rset = pstmt.executeQuery();
            Level level = null;
            String name = null;
            int id = -1;
            while (rset.next()) {
                id = rset.getInt("Level_ID");
                name = rset.getString("Level_Name");
                level = new Level(id, name);
                levelList.add(level);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Permissions from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return levelList;
    }

    public static ArrayList loadFunctionsByGroupID(int groupId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList funcList = new ArrayList();
        ResultSet rset = null;
        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectFunctionsByGroupsId"));
            pstmt.setInt(1, groupId);

            rset = pstmt.executeQuery();
            Functionality functionality = null;

            while (rset.next()) {
                functionality = new Functionality(rset.getInt("Function_ID"),
                rset.getString("Function_Name"),
                groupId,
                ((rset.getInt("FT_View") == 0) ? true : false),
                ((rset.getInt("FT_Modify") == 0) ? true : false),
                ((rset.getInt("FT_Delete") == 0) ? true : false));
                funcList.add(functionality);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Permissions from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return funcList;
    }

    public static ArrayList loadAllUserGroups() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList groupList = new ArrayList();
        ResultSet rset = null;
        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectAllGroups"));

            rset = pstmt.executeQuery();
            int id = -1;
            String name = null;
            UserGroup group = null;
            while (rset.next()) {
                id = rset.getInt("Group_ID");
                name = rset.getString("Group_Name");
                group = new UserGroup(id, name);
                groupList.add(group);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Groups from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return groupList;
    }

    // test
    public static void main(String args[]) {
        try {
            System.out.println("LOADING USERS BY THERE FUNCTION ACCESS... ");
            ArrayList userList = loadUsersWithModifyFunctionAccess("Order Scheduling");
            int size = userList.size();
            System.out.println("Number of User loaded: " + size);
            for (int i=0; i<size; i++) {
                System.out.println(userList.get(i));
            }


            /*ArrayList groupList = loadAllUserGroups();
            if (groupList != null) {
                UserGroup group = null;
                for (int i=0; i<groupList.size(); i++) {
                    group = (UserGroup) groupList.get (i);
                    loadFunctionsByGroupID (group.getId());
                }
            }*/
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * @param firstName
     * @param lastName
     * @param groupName
     * @param loadExpiredAndDisabledUsers
     * @return
     */
    public static ArrayList loadUsersByFirstNameLastNameAndGroup(String firstName, String lastName, String groupName, boolean loadExpiredAndDisabledUsers)throws ScalarException  {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList userList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if (loadExpiredAndDisabledUsers == true) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUsersByFirstNameLastNameAndGroupIncludingExpiredAndDisabledUsers"));
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.selectUsersByFirstNameLastNameAndGroup"));
            }

            pstmt.setString(1, firstName + "%");
            pstmt.setString(2, lastName + "%");
            pstmt.setString(3, groupName);

            rset = pstmt.executeQuery();
            User user = null;

            while (rset.next()) {
                user = new User(rset.getString("User_ID"),
                rset.getString("First_Name"),
                rset.getString("Last_Name"),
                rset.getString("Email"),
                rset.getInt("Group_ID"),
                rset.getInt("Level_ID"));

                user.setGroupName(rset.getString("Group_Name"));
                user.setAddressId(rset.getInt("Address_ID"));
                user.setRegionId(rset.getInt("Region_ID"));
                user.setRegionName(rset.getString("Region_Name"));
                user.setPhoneNumber(rset.getString("Phone_Number"));
                user.setFaxNumber(rset.getString("Fax_Number"));
                user.setExpiresOn(rset.getString("Expires_On"));
                user.setReceiveNotifications((rset.getInt("Send_Notifications") == 0) ? "on" : "off");
                user.setDisabled((rset.getInt("Disabled") == 0) ? true : false);

                userList.add(user);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Users from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return userList;

    }
    /** Added by Sarada on 1st Oct 2003 to check whether userId already exists ot not */

    public static boolean doesUserIdExist(DBTransaction dbTransaction, String userId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalusers = 0;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("select count(*) as total from user_table where user_Id = ?");

            pstmt.setString(1, userId);
            //pstmt.setString (2, methodname );
            //pstmt.setString (3, account);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                totalusers = rs.getInt("total");
            }
            if(totalusers > 0) {
                return true;
            }

            return false;

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not find User Id, \"" + userId + "\" in the database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }


    }
    public static boolean doesRegionIdExist(DBTransaction dbTransaction, String regionId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalusers = 0;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("select count(*) as total from Regions_Table WHERE REGION_NAME = ?");

            pstmt.setString(1, regionId);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                totalusers = rs.getInt("total");
            }
            if(totalusers > 0) {
                return true;
            }

            return false;

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not find region Id, \"" + regionId + "\" in the database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }


    }
    /* Added by  Sarada on 1st Jan 2004 to save Client Info */
    public static int saveClientAddressDetails( DBTransaction dbTransaction, String clientName, String address1, String address2,String address3, String address4, String city, String state, String zipCode, String country, String phoneNo, String faxNo, String email ) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        boolean success = false;
        int addressId = 0;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }


            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientAddressId"));
            rs = pstmt.executeQuery();
            //Modified by Sarada on 10th Feb 2004 to insert/update client Address
            if(rs.next() == false) {

                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertClientAddress"));

                pstmt.setString( 1 , address1  != null ? address1.trim() : "" );
                pstmt.setString( 2 , address2  != null ? address2.trim() : "" );
                pstmt.setString( 3 , city      != null ? city.trim()     : "" );
                pstmt.setString( 4 , state     != null ? state.trim()    : "" );
                pstmt.setString( 5 , zipCode   != null ? zipCode.trim()  : "" );
                pstmt.setString( 6 , country   != null ? country.trim()  : "" );
                pstmt.setString( 7 , phoneNo   != null ? phoneNo.trim()  : "" );
                pstmt.setString( 8 , faxNo     != null ? faxNo.trim()    : "" );
                pstmt.setString( 9 , email     != null ? email.trim()    : "" );
				pstmt.setString( 10 , address3 != null ? address3.trim() : "" );
				pstmt.setString( 11 , address4 != null ? address4.trim() : "" );


                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

				//Query modified by Sarada on 10th March 2004
                pstmt = conn.prepareStatement(SqlQueryMappings.get( "users.getAddressId" ) );
                rs1 = pstmt.executeQuery();
                if ( rs1.next() ) {
                    addressId = rs1.getInt("address_id");
                }

            }
            else {
                addressId = rs.getInt("Address_Id");
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.saveClientAddress"));

                pstmt.setString( 1 , address1  != null ? address1.trim() : "" );
                pstmt.setString( 2 , address2  != null ? address2.trim() : "" );
                pstmt.setString( 3 , city      != null ? city.trim()     : "" );
                pstmt.setString( 4 , state     != null ? state.trim()    : "" );
                pstmt.setString( 5 , zipCode   != null ? zipCode.trim()  : "" );
                pstmt.setString( 6 , country   != null ? country.trim()  : "" );
                pstmt.setString( 7 , phoneNo   != null ? phoneNo.trim()  : "" );
                pstmt.setString( 8 , faxNo     != null ? faxNo.trim()    : "" );
                pstmt.setString( 9 , email     != null ? email.trim()    : "" );
				pstmt.setString( 10 , address3 != null ? address3.trim() : "" );
				pstmt.setString( 11 , address4 != null ? address4.trim() : "" );
                pstmt.setInt    ( 12 , addressId );
                //System.out.println("Address");
                pstmt.executeUpdate();
            }

            success = true;

        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert Client Details into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return  addressId;

    }

    /* Added by  Sarada on 1st Jan 2004 to save Client Info */
    public static int saveClientBankAddressDetails( DBTransaction dbTransaction, String bankName, String bankAddress, String  bankCity, String bankState, String bankZipcode, String bankCountry, String aba, String acct, String swiftCode ,int bankId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        boolean success = false;
        int addressId = 0;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            //From here starts BankInformation saving starts
            //step:1 inserts Bank info in Client_BankInfo Table if it it new Bank Name ie BankId = -1
            if( bankId == -1) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertBankAddress"));

                pstmt.setString( 1 , bankName );
                pstmt.setString( 2 , bankAddress );
                pstmt.setString( 3 , bankCity );
                pstmt.setString( 4 , bankState );
                pstmt.setString( 5 , bankZipcode );
                pstmt.setString( 6 , bankCountry );
                pstmt.setString( 7 , aba );
                pstmt.setString( 8 , acct );
                pstmt.setString( 9 , swiftCode );

                pstmt.execute();
                pstmt.close();
                pstmt = null;

                //step:2 get that BankId returns after insert data into table
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientBankId"));
                rs2 = pstmt.executeQuery();
                while( rs2.next()) {
                    bankId = rs2.getInt("BankId");
                    //System.out.println("finalBankId in DAO "+bankId);
                }
                pstmt.close();
                pstmt = null;
                rs2.close();
            }
            else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateBankAddress"));

                pstmt.setString( 1 , bankName );
                pstmt.setString( 2 , bankAddress );
                pstmt.setString( 3 , bankCity );
                pstmt.setString( 4 , bankState );
                pstmt.setString( 5 , bankZipcode );
                pstmt.setString( 6 , bankCountry );
                pstmt.setString( 7 , aba );
                pstmt.setString( 8 , acct );
                pstmt.setString( 9 , swiftCode );
                pstmt.setInt(10 , bankId );

                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }



            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getBankId"));
            rs2 = pstmt.executeQuery();
            //If there is no bankId in Banking_information table then insert maximumid we get from Client_BankInfo table or update

            if(rs2.next() == false) {
                pstmt.close();
                pstmt = null;
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertBankId"));
                pstmt.setInt( 1 , bankId );
                pstmt.execute();

            }
            else {
                pstmt.close();
                pstmt = null;
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.updateBankId"));
                pstmt.setInt( 1 , bankId );
                pstmt.executeUpdate();
            }

            success = true;




        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert Client Details into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return  bankId;
    }


    /* Added by  Sarada on 1st Jan 2004 to save Client Info */
    public static void saveClientInfo( DBTransaction dbTransaction, String clientName, int addressId, String ccatsNumber, int jitWindow , int shipJitWindow, int sortProcessTime, int assmProcessTime, int locationId ) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        // int addressId = 0;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientAddressId"));
            rs = pstmt.executeQuery();
            //Modified by Sarada on 10th Feb 2004 to insert/update Client info
            if(rs.next() == false) {
                pstmt.close();
                pstmt = null;
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.insertClientInfo"));

            }
            else {
                pstmt.close();
                pstmt = null;
                pstmt = conn.prepareStatement(SqlQueryMappings.get("users.saveClientInfo"));
            }

            pstmt.setString( 1 , clientName != null ? clientName.trim() : "" );
            pstmt.setInt    ( 2 , addressId );
			pstmt.setString ( 3 , ccatsNumber != null ? ccatsNumber.trim() : "" );
            pstmt.setInt    ( 4, jitWindow);
            pstmt.setInt    ( 5, shipJitWindow  );
            pstmt.setInt    ( 6, sortProcessTime  );
            pstmt.setInt    ( 7, assmProcessTime  );
            pstmt.setInt    ( 8, locationId);

            pstmt.executeUpdate();

            success = true;
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert Client Info into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    public static String getClientAddressId() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int addressId = 0;
        String clientName = null;
		String ccatsNumber = null;
        int jitWindow  = 0;
        int shipJitWindow = 0;
        int sortProcessTime = 0;
        int assmProcessTime = 0;
        int shippingLocationId= 0;
        StringBuffer addressIdandName = new StringBuffer();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientAddressId"));
            rs = pstmt.executeQuery();
            while(rs.next()) {
                addressId = rs.getInt("Address_Id");
                addressIdandName = addressIdandName.append(addressId);
                addressIdandName = addressIdandName.append("@");
                clientName = rs.getString("Name");
                if( clientName != null ) {
                    clientName = clientName.trim();
                }

                addressIdandName = addressIdandName.append(clientName);
				addressIdandName = addressIdandName.append("@");
				ccatsNumber = rs.getString("CCATSNumber");
				addressIdandName = addressIdandName.append(ccatsNumber);
                addressIdandName = addressIdandName.append("@");
                jitWindow   = rs.getInt("JIT_WINDOW");
                addressIdandName = addressIdandName.append(jitWindow);
                addressIdandName = addressIdandName.append("@");
                shipJitWindow    = rs.getInt("Ship_JIT_WINDOW");
                addressIdandName = addressIdandName.append(shipJitWindow);
                addressIdandName = addressIdandName.append("@");
                sortProcessTime  = rs.getInt("SORT_PROCESS_TIME");
                addressIdandName = addressIdandName.append( sortProcessTime );
                addressIdandName = addressIdandName.append("@");
                assmProcessTime  = rs.getInt("ASSM_PROCESS_TIME");
                addressIdandName = addressIdandName.append( assmProcessTime );
                addressIdandName = addressIdandName.append("@");
                shippingLocationId = rs.getInt("DEFAULT_SHIPPING_LOCTION_ID");
                addressIdandName = addressIdandName.append( shippingLocationId );

            }

            success = true;
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Client AddresId from database", sqle);
        }
        finally {
            // if (dbTransaction == null) {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}
            //}
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return addressIdandName.toString();
    }

    public static Address loadClientAddressDetails( DBTransaction dbTransaction, int addressId ) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        Address address = new  Address();

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();

            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientAddress"));
            pstmt.setInt( 1, addressId );
            rs = pstmt.executeQuery();
            if(rs.next()) {

                address.setAddress1(rs.getString("Address_1"));
                address.setAddress2(rs.getString("Address_2"));
				address.setAddress3(rs.getString("Address_3"));
				address.setAddress4(rs.getString("Address_4"));
                address.setCity(rs.getString("City"));
                address.setState(rs.getString("State"));
                address.setZipCode(rs.getString("ZipCode"));
                address.setCountry(rs.getString("Country"));
                address.setPhoneNumber(rs.getString("Phone_Number"));
                address.setFaxNumber(rs.getString("Fax_Number"));
                address.setEmail(rs.getString("Email"));

            }

            success = true;

        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Client Address and Bank Address from database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return address;
    }

    public static HashMap loadClientBankAddressDetails( DBTransaction dbTransaction, int bankId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSet rs1 = null;ResultSet rs2 = null;
        boolean success = false;
        BankInfo bankInfo = null;
        HashMap bankInfoMap = new HashMap();

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();

            }
            else {
                conn = dbTransaction.getConnection();
            }


            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientBankAddress"));
            rs2 = pstmt.executeQuery();
            while(rs2.next())
            {
				bankInfo = new BankInfo();
                bankInfo.setBankId(rs2.getInt("BankId"));
                bankInfo.setBankName( rs2.getString("BankName") );
                bankInfo.setAddress  ( rs2.getString("Address") );
                bankInfo.setCity( rs2.getString("City") );
                bankInfo.setState( rs2.getString("State") );
                bankInfo.setZipCode( rs2.getString("ZipCode") );
                bankInfo.setCountry( rs2.getString("Country") );
                bankInfo.setAba( rs2.getString("ABa") );
                bankInfo.setAcct( rs2.getString("Acct") );
                bankInfo.setSwiftCode( rs2.getString("SwiftCode") );
				bankInfoMap.put( ""+rs2.getInt("BankId"), bankInfo );
            }

            success = true;

        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Bank Address from database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return bankInfoMap;
    }

    public static ArrayList getAllBankNames() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList bankNames = new ArrayList();

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getAllBankNames"));
            rs = pstmt.executeQuery();
            bankNames.add(new org.apache.struts.util.LabelValueBean("--- Select Bank ---","0"));
            while(rs.next())
            {
                String bankName = rs.getString("BankName");
                int bankId = rs.getInt("BankId");
                bankNames.add(new org.apache.struts.util.LabelValueBean(bankName,Integer.toString(bankId)));
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get BankNames from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return bankNames;
    }

    public static int loadBankId()throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int bankId = 0;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getBankId"));
            rs = pstmt.executeQuery();
            while(rs.next()) {
                bankId = rs.getInt("BankId");
            }

            success = true;
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Client AddresId from database", sqle);
        }
        finally {
            // if (dbTransaction == null) {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}
            //}
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return bankId;

    }

    //------------------------------------------------------------------------------
    /* chakravarthy :
     * return  ClientInfo
     * ClientInfo gives address object, clientId and client name
     * retrieve the fields of address and client tables
     * put them in addressclient object and return the object
     * that consists of address object, clientid and client name
     *
     */
	public static ClientInfo getClientAddressInfo() throws ScalarException
	{

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;

		ClientInfo clientInfo = null;
		Address address = null;

		try
		{
			conn = DataSource.getConnection();
			//pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getClientInfo"));
			pstmt = conn.prepareStatement("select a.Address_ID, a.Address_1, a.Address_2, a.Address_3, a.Address_4, a.City, a.State, a.Zipcode, a.Country, a.Phone_Number,a.Fax_Number, a.Cell_Number, a.Email, a.Timestamp, a.Contact_Person, ct.Client_ID,ct.Name, ct.CCATSNumber, ct.JIT_WINDOW, Release_No from Address a, Client_Table ct where a.Address_ID=ct.Address_ID");
			rset = pstmt.executeQuery();

			while (rset.next())
			{
				address = new Address();
				address.setAddressId( rset.getInt( "Address_ID" ) );
				address.setAddress1	( rset.getString( "Address_1" ) );
				address.setAddress2	( rset.getString( "Address_2" ) );
				address.setAddress3	( rset.getString( "Address_3" ) );
				address.setAddress4	( rset.getString( "Address_4" ) );
				address.setCity	    ( rset.getString( "City" ) );
				address.setState	( rset.getString( "State" ) );
				address.setZipCode	( rset.getString( "Zipcode" ) );
				address.setCountry	( rset.getString( "Country" ) );
				address.setPhoneNumber( rset.getString( "Phone_Number" ) );
				address.setFaxNumber( rset.getString( "Fax_Number" ) );
				address.setCellNumber( rset.getString( "Cell_Number" ) );
				address.setEmail	( rset.getString( "Email" ) ) ;
				clientInfo = new ClientInfo ( address, rset.getInt( "Client_ID" ), rset.getString( "Name" ), rset.getString("CCATSNumber"), rset.getInt("JIT_WINDOW") );
                clientInfo.setGeneratedRelShipLotNo( rset.getString("Release_No").trim() );
                if( clientInfo != null && clientInfo.getClientName() != null )
                {
                    clientInfo.setClientName( clientInfo.getClientName().trim() );
                }
			}
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException("Could not load Client Info from database", sqle);
		}
		finally {
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return clientInfo;
	}
    public static void updateAssyReleaseNo( String releaseNo) throws Exception
    {
        Connection conn = null;
		PreparedStatement pstmt = null;
        boolean success = false;

            conn = DataSource.getConnection();
        try
        {
            pstmt = conn.prepareStatement("UPDATE CLIENT_TABLE SET RELEASE_NO = ? ");
            pstmt.setString(1, releaseNo );

            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            success =true;
        }
        catch (Exception e)
        {
           e.printStackTrace();
		   throw new ScalarException("Could not update releaseNo Client table from database", e);
        }
        finally
        {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
    }
    // Modified by Prabhanjan (30 May 2004). Added new parameters quotationPrefix and quotationLength
	//Added by Sarada on 29th March 2004 to save sales order prefix and length in Sales_Info table
    //public static void saveSalesOrderInfo ( DBTransaction dbTransaction , String salesOrderPrefix , int salesOrderLength )throws ScalarException
	public static void saveSalesOrderInfo ( DBTransaction dbTransaction , String salesOrderPrefix , int salesOrderLength, String quotationPrefix, int quotationLength )throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean success = false;

		try
		{
			if(dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.getSalesOrderInfo"));
			rs = pstmt.executeQuery();
			if( rs.next() == false)
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.insertSalesOrderInfo"));
			}
			else
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.updateSalesOrderInfo"));
			}

			pstmt.setString ( 1 , salesOrderPrefix );
			pstmt.setInt ( 2 , salesOrderLength );

            // Added by Prabhanjan (30 May 2004)
            pstmt.setString( 3, quotationPrefix );
            pstmt.setInt( 4, quotationLength );
			pstmt.executeUpdate();

			success = true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ScalarException("Could not save salesOrder Prefix info ", e);
		}
		finally
		{
			if(dbTransaction == null)
			{
				try
				{
					if(conn != null)
					{
						if(success == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}catch(SQLException ignore){}
			}
			if(rs != null)
			{
				try{ rs.close(); }catch(SQLException ignore){}
			}
			if(pstmt != null)
			{
				try{ pstmt.close(); }catch(SQLException ignore){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(SQLException ignore){}
			}
		}

	}//method


    //Added by Rohan on July 22nd 2004
    /**
     * This method saves the sales setup information into the table Sales_Info
     *
     * @param dbTransaction
     * @param salesInfo
     * @throws ScalarException
     */
    public static void saveSalesOrderInfo ( DBTransaction dbTransaction , SalesInfo salesInfo )throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean success = false;

		try
		{
			if(dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.saveSalesOrderInfo" ) );

			pstmt.setString( 1, salesInfo.getSalesOrderPrefix() );
			pstmt.setInt(    2, salesInfo.getSalesOrderLength() );
            pstmt.setString( 3, salesInfo.getQuotationPrefix() );
            pstmt.setInt(    4, salesInfo.getQuotationLength() );
            pstmt.setString( 5, salesInfo.getRmaPrefix() );
            pstmt.setInt(    6, salesInfo.getRmaLength() );
            pstmt.setString( 7, salesInfo.getPsPrefix() );
            pstmt.setInt(    8, salesInfo.getPsLength() );
            pstmt.setString( 9, salesInfo.getPurchaseOrderNoPrefix() );
            pstmt.setInt(    10, salesInfo.getPurchaseOrderNoLength() );
            pstmt.setString( 11, salesInfo.getAutoReceiptNoPrefix() );
            pstmt.setInt(    12, salesInfo.getAutoReceiptNoLength() );
            pstmt.setString( 13, salesInfo.getProgramReleasePrefix() );
            pstmt.setInt(    14, salesInfo.getProgramReleaseLength() );
            pstmt.setString( 15, salesInfo.getSortReleasePrefix() );
            pstmt.setInt(    16, salesInfo.getSortReleaseLength() );
            pstmt.setString( 17, salesInfo.getAssmReleasePrefix() );
            pstmt.setInt(    18, salesInfo.getAssmReleaseLength() );
            pstmt.setString( 19, salesInfo.getShipRequestPrefix() );
            pstmt.setInt(    20, salesInfo.getShipRequestLength() );
            pstmt.setString( 21, salesInfo.getTestReleasePrefix() );
            pstmt.setInt(    22, salesInfo.getTestReleaseLength() );

            pstmt.execute();

			success = true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ScalarException("Could not save sales setup information ", e);
		}
		finally
		{
			if(dbTransaction == null)
			{
                if(conn != null)
				{
				    if(success == true)
					{
                        try
                        {
				            conn.commit();
                        }
                        catch( SQLException e )
                        {
                            e.printStackTrace();
			                throw new ScalarException( "Error while committing the save sales setup information", e );
                        }
                    }
					else
					{
                        try
                        {
							conn.rollback();
                        }
                        catch( SQLException e )
                        {
                            e.printStackTrace();
			                throw new ScalarException( "Error while rolling back the save sales setup information", e );
                        }
					}
                }
			}
			if(rs != null)
			{
				try{ rs.close(); }catch(SQLException ignore){}
			}
			if(pstmt != null)
			{
				try{ pstmt.close(); }catch(SQLException ignore){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(SQLException ignore){}
			}
		}

	}

	public static SalesInfo getSalesOrderInfo ( DBTransaction dbTransaction ) throws ScalarException
	{
		Connection conn =  null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean success = false;
		SalesInfo salesInfo = null;

		try
		{

			if(dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.getSalesOrderInfo"));
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				salesInfo = new SalesInfo();
				salesInfo.setSalesOrderPrefix(      rs.getString("Sales_Order_Prefix") );
				salesInfo.setSalesOrderLength(      rs.getInt("Sales_Order_Length") );
                salesInfo.setQuotationPrefix(       rs.getString("Quotation_Prefix") );
                salesInfo.setQuotationLength(       rs.getInt("Quotation_Length") );
                salesInfo.setRmaPrefix(             rs.getString("RMA_Prefix") );
                salesInfo.setRmaLength(             rs.getInt("RMA_Length") );
                salesInfo.setPsPrefix(              rs.getString("PS_Prefix") );
                salesInfo.setPsLength(              rs.getInt("PS_Length") );
                salesInfo.setPurchaseOrderNoPrefix( rs.getString("Purchase_Order_Prefix") );
                salesInfo.setPurchaseOrderNoLength( rs.getInt("Purchase_Order_Length") );
                salesInfo.setAutoReceiptNoPrefix( rs.getString("Auto_Receipt_Prefix") );
                salesInfo.setAutoReceiptNoLength( rs.getInt("Auto_Receipt_Length") );
                salesInfo.setProgramReleasePrefix( rs.getString("Program_Release_Prefix") );
                salesInfo.setProgramReleaseLength( rs.getInt("Program_Release_Length") );
                salesInfo.setSortReleasePrefix( rs.getString("Sort_Release_Prefix") );
                salesInfo.setSortReleaseLength( rs.getInt("Sort_Release_Length"));
                salesInfo.setAssmReleasePrefix( rs.getString("Assm_Release_Prefix") );
                salesInfo.setAssmReleaseLength( rs.getInt("Assm_Release_Length") );
                salesInfo.setShipRequestPrefix( rs.getString("Shipping_Request_Prefix") );
                salesInfo.setShipRequestLength( rs.getInt("Shipping_Request_Length") );
                salesInfo.setTestReleasePrefix( rs.getString("Test_Release_Prefix") );
                salesInfo.setTestReleaseLength( rs.getInt("Test_Release_Length") );
            }
			success = true;

		}catch(Exception e){
			e.printStackTrace();
			throw new ScalarException ("Cound not get Sales Order Prefix Info from Databse", e);
		}
		finally
			{
				if(dbTransaction == null)
				{
					try
					{
						if(conn != null)
						{
							if(success == true)
							{
								conn.commit();
							}
							else
							{
								conn.rollback();
							}
						}
					}catch(SQLException ignore){}
				}
				if(rs != null)
				{
					try{ rs.close(); }catch(SQLException ignore){}
				}
				if(pstmt != null)
				{
					try{ pstmt.close(); }catch(SQLException ignore){}
				}
				if(conn != null)
				{
					try{ conn.close(); }catch(SQLException ignore){}
				}
			}


		return salesInfo;
	}

    //Added by Phani 24/07/2004
    public static ArrayList loadAllUsersWithNameAndEmail() throws ScalarException {

        ArrayList users = new ArrayList();
        User user = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success=false;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.getAllUsers"));
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                user = new User();
                user.setUserId( rs.getString( "User_ID") );
                user.setFirstName( rs.getString("First_Name") );
                user.setLastName( rs.getString("Last_Name") );
                user.setEmail( rs.getString("Email") );
                users.add( user );
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Users from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return users;
    }

    /**
     * returns the User MailIDs Array List
     * @param functionName
     * @return emailList
     * @throws ScalarException
     */
    public static ArrayList loadUserMailIDsByAuthorization(String functionName) throws ScalarException {

        ArrayList emailList = new ArrayList();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success=false;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("users.loadUserMailIDsByAuthorization"));
            pstmt.setString(1, functionName);
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                emailList.add( rs.getString("Email") );
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not load User MailIDs By Authorization from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return emailList;
    }

   //Added by Kumaraswamy on 20th Dec, 2004.
     public static ArrayList loadEmailsByType( int type ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList emailsList = new ArrayList();
        String query = null;

        switch( type )
        {
            case 0:
                query = "operations.packingslip.loadReceivePackingSlipMails";
                break;

            case 1:
                query = "finance.proformaInvoice.loadReceiveProformaInvoiceMails";
                break;

            case 2:
                query = "sales.saleOrder.loadReceiveShipAlertMails";
                break;

            case 3:
                query = "sales.salesOrder.loadReceiveSalesOrderAcknowledgeMails";
                break;

            default:
                break;
        }

        if( query != null )
        {
            try
            {
                conn = DataSource.getConnection();

                pstmt = conn.prepareStatement( SqlQueryMappings.get( query ) );
                rset = pstmt.executeQuery();

                while( rset.next() )
                {
                     emailsList.add( rset.getString( "NameAndMailId" ) );
                }
            }
            catch( Exception e )
            {
                throw new ScalarException( "Could not retrieve email list from database", e );
            }
            finally
            {
                if( rset != null )
                {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if( pstmt != null )
                {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
                if( conn != null )
                {
                    try { conn.close(); } catch (SQLException ignore) {}
                }
            }
        }

        return emailsList;
    }
    public static int getUserGroupId( DBTransaction dbTransaction, String groupName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int userGroupId = 0;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT GROUP_ID FROM USER_GROUP_TABLE WHERE GROUP_NAME = ?" );
            pstmt.setString ( 1, groupName );

            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                userGroupId = rset.getInt ( "GROUP_ID" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Group Id from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return userGroupId;
    }
    public static void saveUserMapValues ( DBTransaction dbTransaction, String userId, int customerId, int vendorId, int salesPersonId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "DELETE FROM USER_MAP_TABLE WHERE USER_ID = ?" );
            pstmt.setString ( 1, userId );
            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement( "INSERT INTO USER_MAP_TABLE(USER_ID, CUSTOMER_ID, VENDOR_ID, SALESPERSON_ID) VALUES( ?, ?, ?, ? )" );
            pstmt.setString ( 1, userId );
            pstmt.setString ( 2, customerId <= 0 ? null : Integer.toString( customerId ) );
            pstmt.setString ( 3, vendorId <= 0 ? null : Integer.toString( vendorId ) );
            pstmt.setString ( 4, salesPersonId <= 0 ? null : Integer.toString( salesPersonId ) );

            pstmt.executeUpdate();

            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save user map values", e );
        }
        finally
        {
            if ( dbTransaction == null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }

    public static String[] loadAllApproversEmails( DBTransaction dbTransaction, int acknowledgmentType, String salesOrderOrQuote ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String emails           = "";

        try
        {

            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            if( "Quotation".equalsIgnoreCase( salesOrderOrQuote ) )
            {
                //pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail FROM User_Table UT, User_Group_Table UGT, Function_Access_Table FAT, Functionality_Table FT WHERE FT.Function_Name in ( 'Approve Quotation' ) AND FAT.FAT_Modify = 0 AND FT.Function_ID = FAT.Function_ID AND UT.User_ID = FAT.User_ID AND UGT.Group_ID = UT.Group_ID AND expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
                pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail, Rma_Ack_Mail FROM User_Table UT, User_Group_Table UGT, Function_Access_Table FAT, Functionality_Table FT WHERE FT.Function_Name in ( 'Approve Quotation' ) AND FAT.FAT_Modify = 0 AND Send_notifications = 0 AND FT.Function_ID = FAT.Function_ID AND UT.User_ID = FAT.User_ID AND UGT.Group_ID = UT.Group_ID AND expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
            }
            else
            {
                //pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail FROM User_Table UT, User_Group_Table UGT, Function_Access_Table FAT, Functionality_Table FT WHERE FT.Function_Name in ( 'Finance Holds/Release', 'Marketing Holds/Release', 'Sales Holds/Release', 'Operations Holds/Release' ) AND FAT.FAT_Modify = 0 AND FT.Function_ID = FAT.Function_ID AND UT.User_ID = FAT.User_ID AND UGT.Group_ID = UT.Group_ID AND expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
                pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail, Rma_Ack_Mail FROM User_Table UT WHERE Send_notifications = 0 and expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
            }

            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                emails = emails + rset.getString( "Email" ) + ",";
            }

            if( emails != null && emails.length() > 0 )
            {
                emails =  emails.substring( 0 , emails.length() - 1 );
            }
            emails = StringUtils.removeDuplicateEmailIds(emails);
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Users Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }

    public static ArrayList loadAllApproversEmailsList( DBTransaction dbTransaction, int acknowledgmentType, String salesOrderOrQuote ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        ContactPerson contactperson;
        ArrayList usersList         = new ArrayList();

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if( "Quotation".equalsIgnoreCase( salesOrderOrQuote ) )
            {
                //pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail FROM User_Table UT, User_Group_Table UGT, Function_Access_Table FAT, Functionality_Table FT WHERE FT.Function_Name in ( 'Approve Quotation' ) AND FAT.FAT_Modify = 0 AND FT.Function_ID = FAT.Function_ID AND UT.User_ID = FAT.User_ID AND UGT.Group_ID = UT.Group_ID AND expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
                pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail, Rma_ack_mail FROM User_Table UT, User_Group_Table UGT, Function_Access_Table FAT, Functionality_Table FT WHERE FT.Function_Name in ( 'Approve Quotation' ) AND FAT.FAT_Modify = 0 AND Send_notifications = 0 AND FT.Function_ID = FAT.Function_ID AND UT.User_ID = FAT.User_ID AND UGT.Group_ID = UT.Group_ID AND expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
            }
            else
            {
                //pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail FROM User_Table UT, User_Group_Table UGT, Function_Access_Table FAT, Functionality_Table FT WHERE FT.Function_Name in ( 'Finance Holds/Release', 'Marketing Holds/Release', 'Sales Holds/Release', 'Operations Holds/Release' ) AND FAT.FAT_Modify = 0 AND FT.Function_ID = FAT.Function_ID AND UT.User_ID = FAT.User_ID AND UGT.Group_ID = UT.Group_ID AND expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
                pstmt = conn.prepareStatement( "SELECT DISTINCT UT.User_ID, First_Name, Last_Name, Email, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Quote_Ack_Mail, Rma_ack_mail FROM User_Table UT WHERE Send_notifications = 0 and expires_on > CURRENT_TIMESTAMP AND DISABLED = 1" );
            }

            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                contactperson = new ContactPerson();

                contactperson.setIsChecked      ( true );
                contactperson.setContactPerson  ( rset.getString( "First_Name" ) );
                contactperson.setEmail          ( rset.getString( "Email" ) );
                contactperson.setContactPersonType ( "Internal User" );
                contactperson.setSalesAckMail   ( rset.getString( "Sales_Ack_Mail" ) != null &&  rset.getString("Sales_Ack_Mail").equals("0") ? true : false );
                contactperson.setShipAlerts     ( rset.getString( "Ship_Alerts" ) != null &&  rset.getString("Ship_Alerts").equals("0") ? true : false );
                contactperson.setProformaInvoice( rset.getString( "Proforma_Invoice_Mail" ) != null &&  rset.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                contactperson.setQuoteAckMail   ( rset.getString( "Quote_Ack_Mail" ) != null &&  rset.getString("Quote_Ack_Mail").equals("0") ? true : false );
                contactperson.setRmaAckMail     ( rset.getString( "Rma_Ack_Mail" ) != null &&  rset.getString("Rma_Ack_Mail").equals("0") ? true : false );
                usersList.add( contactperson );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Users Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return usersList;
    }
    
    public static String getUsersEmailsForRMA( DBTransaction dbTransaction ) throws ScalarException
    {
	    Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rs                = null;

        String usersEmails  = null;
        boolean success     = false;
        try
		{
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //This statement is for getting the Customer Contact person emails
            pstmt = conn.prepareStatement("SELECT UT.User_ID, Password, First_Name, Last_Name, Email, Level_ID, Address_ID, RT.Region_ID, RT.Region_Name, Phone_Number, Fax_Number, UT.Group_ID, UGT.Group_Name, convert(varchar, Expires_On, 101) as Expires_On, Send_Notifications, UT.Disabled, Sales_Ack_Mail,Ship_Alerts,Proforma_Invoice_Mail,Packing_Slip_Mail, Quote_Ack_Mail, Rma_Ack_Mail, USER_TYPE, UMT.CUSTOMER_ID, UMT.VENDOR_ID, UMT.SALESPERSON_ID FROM User_table UT INNER JOIN User_Group_Table UGT ON UT.Group_ID = UGT.Group_ID INNER JOIN Regions_Table RT ON UT.Region_ID = RT.Region_ID LEFT OUTER JOIN USER_MAP_TABLE UMT ON UT.USER_ID = UMT.USER_ID WHERE  UT.Disabled = 1 and Rma_Ack_Mail=0 and expires_on >= convert(varchar, CURRENT_TIMESTAMP, 101) and (Email IS NOT NULL) AND (Email <> '')");
            //pstmt = conn.prepareStatement("SELECT DISTINCT  Email, Rma_Ack_Mail  FROM Customer_Contact_Persons_Table WHERE  (Email IS NOT NULL) AND (Email <> '') and Deactivated <> 1 and Rma_Ack_Mail=0");
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                if ( usersEmails == null || usersEmails.trim().length() == 0 )
                {
                    usersEmails = rs.getString( "Email" ) ;
                }
                else
                {
                   usersEmails = usersEmails + Mailer.EMAIL_SEPARATOR + rs.getString("Email") ;
                }
            }
            if( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            success = true;
        }
        catch (Exception sqlex)
        {
             sqlex.printStackTrace();
             throw new ScalarException( "Could not get RMA Users emails from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return usersEmails;
    }
     public static ArrayList loadEmailsByGroupName( DBTransaction dbTransaction, String groupName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList emails = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT Email,User_Table.Group_ID, User_Group_Table.Group_Name, convert(varchar, Expires_On, 101) as Expires_On,  User_Table.Disabled FROM User_table, User_Group_Table WHERE User_Table.Group_ID = User_Group_Table.Group_ID and User_Table.Disabled = 1 and expires_on > CURRENT_TIMESTAMP and User_Group_Table.Group_Name=?"  );
            pstmt.setString(1, groupName);
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                emails.add ( rset.getString ( "EMAIL" ) );
            }

        }
        catch ( SQLException e )
        {
            throw new ScalarException( "Could not load Emails", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ){}
            }
        }
        HashSet set = new HashSet(emails);
        Object[] obj = set.toArray();
        emails.clear();
        emails.addAll(Arrays.asList(obj));
        return emails;
    }
    public static ArrayList loadExpiryUsersReprot(DBTransaction dbTransaction) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
        int colCount            = 0;

        ArrayList usersList    = new ArrayList();
        ArrayList colList           = new ArrayList();
        DBColumn dbColumn           = null;

        try
        {
            if (dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            int noOfdays = ConfigManager.getExpiryNoOfDays();
            pstmt = conn.prepareStatement("{ CALL SP_USER_EXPIRY_REPORT_PROC(?) }");
            pstmt.setInt(1, noOfdays);
            rset = pstmt.executeQuery();

            colCount = rset.getMetaData().getColumnCount();

            for (int j = 1; j <= colCount; j++)
            {
                colList.add(rset.getMetaData().getColumnName(j));
            }

            usersList.add(colList);

            while (rset.next())
            {
                colList = new ArrayList();
                for (int i = 1; i <= colCount; i++)
                {
                    dbColumn = new DBColumn(rset.getMetaData().getColumnName(i), rset.getObject(i), rset.getMetaData().getColumnType(i));
                    colList.add(dbColumn);
                }
                usersList.add(colList);
            }
            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Could not load Expiry usersList records", e);
        }
        finally
        {
            if (dbTransaction == null)
            {
                if (conn != null)
                {
                    try
                    {
                        if (success)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    } catch (SQLException e) {
                    }
                }
            }
            if (rset != null)
            {
                try
                {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return usersList;
    }


}